<?php

namespace App\Models;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redis;
use App\Http\Controllers\Jobs\ExcelTaskController;


//库存周转相关类
class InventoryTurnsModel extends BaseModel
{

    public function GetZgSpuList($params){
        $res =  db::table('cloudhouse_contract')->groupby('spu_id')->select('spu_id')->get()->toarray();
        $spu_ids = array_column($res,'spu_id');
        $list = db::table('self_spu')->whereIn('id',$spu_ids);
        if(isset($params['spu'])){
            $spu = $params['spu'];
            // $s = db::table('self_spu')->where('spu',$spu)->orwhere('old_spu',$spu)->get()->toarray();
            // $sids =  array_column($s,'id');
            // $res = $res->whereIn('spu_id',$sids);
            $list =  $list ->where(function($query) use($spu){
                $query->where('spu','like','%'.$spu.'%')->orwhere('old_spu','like','%'.$spu.'%');
            });
        }
        
        $list =  $list->select('spu','old_spu','id')->get();
       
        return $this->SBack($list);
    }
    //走柜导入
    //验证  cloudhouse_custom_sku 必须有
    public function ZgImport($params){
        $file = $params['file'];
        $spu_id = $params['spu_id'];
        $shop_id = $params['shop_id'];


        $spures = $this->GetSpu($spu_id);

        if(!file_exists($file)){
            return [ 'type' => 'fail','msg' => '无此文件，当前路径'.dirname(__FILE__)];
        }

        $extension = $file->getClientOriginalExtension();
        if ($extension == 'csv') {
            $PHPExcel = new \PHPExcel_Reader_CSV();
        } elseif ($extension == 'xlsx') {
            $PHPExcel = new \PHPExcel_Reader_Excel2007();
        } else {
            $PHPExcel = new \PHPExcel_Reader_Excel5();
        }

        $PHPExcelLoad = $PHPExcel->load($file);
        $Sheet = $PHPExcelLoad->getSheet(0);
        $add_list = array();
        /**取得一共有多少行*/
        $allRow = $Sheet->getHighestRow();
        $time = time();

        $return = [];
        for ($j = 2; $j <= $allRow; $j++) {
            $one = [];
            $color = trim($Sheet->getCellByColumnAndRow(0, $j)->getValue());
            $colors = db::table('self_color_size')->where('identifying',$color)->first();
            if(!$colors){
                return $this->FailBack('无此颜色'.$color);
            }
            $color_id = $colors->id;
            $size = trim($Sheet->getCellByColumnAndRow(1, $j)->getValue());
            $spu =  $spures['spu'];
            
            $cus = db::table('self_custom_sku')->where('spu_id',$spu_id)->where('size',$size)->where('color_id',$color_id)->first();
            if(!$cus){
                return $this->FailBack('无符合库存sku'.$spu.'-'.$color.'-'.$size);
            }
            $custom_sku = $cus->custom_sku;
            if(!empty($cus->old_custom_sku)){
                $custom_sku = $cus->old_custom_sku;
            }
            $custom_sku_id = $cus->id;

            $cloudhouse = db::table('cloudhouse_custom_sku')->where('custom_sku_id',$custom_sku_id)->first();
            if(!$cloudhouse){
                return $this->FailBack('暂无合同数据'.$spu.'-'.$color.'-'.$size);
            }

            $num = trim($Sheet->getCellByColumnAndRow(2, $j)->getValue());
            $factory_no = trim($Sheet->getCellByColumnAndRow(3, $j)->getValue());
            $box_num = trim($Sheet->getCellByColumnAndRow(4, $j)->getValue());
            $box_weight = trim($Sheet->getCellByColumnAndRow(5, $j)->getValue());
            $box_size = trim($Sheet->getCellByColumnAndRow(6, $j)->getValue());

            $skus = db::table('self_sku')->where('custom_sku_id',$custom_sku_id)->where('shop_id', $shop_id)->first();
            if(!$skus){
                $fnsku = '';
                return $this->FailBack('该店铺无此数据'.$spu.'-'.$color.'-'.$size.'-店铺'.$shop_id);
            }else{
                $product = db::table('product_detail')->where('sku_id',$skus->id)->first();
                if($product){
                    $fnsku = $product->fnsku;
                }
            }

            $sku = $skus->sku;

            if(!empty($skus->old_sku)){
                $sku = $skus->old_sku;
            }
            $one['name'] = $cus->name;
            $one['fnsku'] = $fnsku;
            $one['color'] = $color;
            $one['color_id'] = $color_id;
            $one['size'] = $size;
            $one['spu'] = $spu;
            $one['spu_id'] = $spu_id;
            $one['custom_sku'] = $custom_sku;
            $one['custom_sku_id'] = $custom_sku_id;
            $one['num'] = $num;
            $one['factory_no'] = $factory_no;
            $one['box_num'] = $box_num;
            $one['box_weight'] = $box_weight;
            $one['box_size'] = $box_size;
            $one['shop_id'] = $shop_id;
            $one['sku'] =  $sku;
            $one['sku_id'] =  $skus->id;
            $return[] = $one;

        }
        return $this->SBack($return);
        // return $this->SBack([]);
    }
    //cloudhouse_record type_detail:6.fba出库  price取移动加权6的价格  如果无 则取cloudhouse_custom_sku  最新的价格 
    //库存读self_custom_sku  deposit_inventory
    //添加走柜
    public function SaveZg($params){

        //走柜任务
        $task_insert['user_id'] = $params['user_id'];
        $task_insert['create_time'] = date('Y-m-d H:i:s');
        $task_insert['shop_id'] = $params['data'][0]['shop_id'];

        db::beginTransaction();    //开启事务
        $zg_id = db::table('self_zg')->insertGetid($task_insert);

        foreach ($params['data'] as $v) {
            $insert['shop_id'] = $v['shop_id'];
            # code...
            $insert['zg_id'] = $zg_id;
            //颜色
            $insert['color'] = $v['color'];
            $insert['color_id'] = $v['color_id'];
            //尺码
            $insert['size'] = $v['size'];
            //数量
            $insert['num'] = $v['num'];
            //fnsku
            $insert['fnsku'] = $v['fnsku'];
            //spu
            $insert['spu'] = $v['spu']; 
            $insert['spu_id'] = $v['spu_id'];
            //custom_sku
            $insert['custom_sku'] = $v['custom_sku']; 
            $insert['custom_sku_id'] = $v['custom_sku_id'];
            //中文名
            $insert['name'] = $v['name'];
            //工厂箱号
            $insert['factory_no'] = $v['factory_no'];
            //箱数
            $insert['box_num'] = $v['box_num'];
            //每箱毛重
            $insert['box_weight'] = $v['box_weight'];
            //箱规
            $insert['box_size'] = $v['box_size'];

            $insert['sku'] = $v['sku'];
            $insert['sku_id'] = $v['sku_id'];

            $inzg = db::table('self_zg_detail')->insert($insert);
            if(!$inzg){
                return $this->FailBack('走柜失败-01');
                db::rollback();// 回调
            }

            //减少库存
            // $cus = db::table('self_custom_sku')->where('id',$v['custom_sku_id'])->first();
            // $deposit_inventory = $cus->deposit_inventory - $insert['num'];
            // $donum = db::table('self_custom_sku')->where('id',$v['custom_sku_id'])->update(['deposit_inventory'=>$deposit_inventory]);
            // if(!$donum){
            //     return $this->FailBack('走柜失败-02');
            //     db::rollback();// 回调
            // }

            // //存入库存日志 cloudhouse_record
            // //cloudhouse_record type_detail:6.fba出库  price取移动加权6的价格  如果无 则取cloudhouse_custom_sku  最新的价格 
            // $log['type'] = 1;
            // $log['spu'] = $v['spu'];
            // $log['custom_sku'] = $v['custom_sku'];
            // $log['spu_id'] = $v['spu_id'];
            // $log['custom_sku_id'] = $v['custom_sku_id'];
            // $log['num'] = $v['num'];
            // $log['createtime'] = date('Y-m-d H:i:s',time());
            // $log['type_detail'] = 6;
            // $log['shop_id'] = $v['shop_id'];
            // $log['user_id'] = $params['user_id'];
            // $log['now_inventory'] = $deposit_inventory;

            // $log['order_no'] = '';//订单号
            // $log['warehouse_id'] = '';//仓库id1.同安仓2.泉州仓3.云仓4.工厂虚拟仓5.其它
            // $log['price'] = '';//成本价
            // $log['now_total_price'] = '';//仓库结余资产
            // db::table('cloudhouse_record')->insert($log);

        }
        db::commit();
        return $this->SBack([]);
    }

    //获取走柜详情
    public function GetZg($params){
        $id = $params['id'];;
        $res = db::table('self_zg_detail')->where('zg_id',$id)->get();
        $task  = db::table('self_zg')->where('id',$id)->first();
        $task->account = $this->GetUsers($task->user_id)['account'];

        $data['data'] = $res;
        $data['task'] = $task;

        $sizes = [];
        $gyres = [];
        foreach ($res as $key => $v) {
            $sizes[$v->size] = $v->size;
        }
        foreach ($res as $key => $v) {
            # code...
            $k = $v->spu.$v->color_id;
            if($key==0){
                $v->fba_info = $task->fba_content;
                $gyres[$k]['fba_info'] = $task->fba_content;
            }
            $v->shop_name = $this->GetShop($v->shop_id)['shop_name'];
         
            $gyres[$k]['spu'] = $v->spu;
            $gyres[$k]['color'] = $v->spu;
            $gyres[$k]['factory_no'] = $v->factory_no;
            $gyres[$k]['box_no'] = $v->box_no;
            $gyres[$k]['box_num'] = $v->box_num;
            $gyres[$k]['box_weight'] = $v->box_weight;
            $gyres[$k]['box_size'] = $v->box_size;
            foreach ($sizes as $sv) {
                # code...
                if($sv==$v->size){
                    $gyres[$k][$sv] = $v->num;
                }
               
            }

        }

        $gyres = array_values($gyres);

        $posttype = $params['posttype']??1;
        


        if($posttype==2){

            $p['title']='走柜详情'.rand(1,100).time();
            $p['title_list']  = [
                'spu'=>'款号',
                'color'=>'颜色',
                'custom_sku'=>'库存sku',
                'sku'=>'渠道sku',
                'name'=>'中文名',
                'shop_id'=>'店铺',
                'size'=>'尺码',
                'fnsku'=>'FNSKU',
                'num'=>'数量',
                'factory_no'=>'工厂箱号',
                'box_no'=>'箱唛号',
                'box_num'=>'箱数',
                'box_weight'=>'每箱毛重',
                'box_size'=>'箱规',
                'fba_info'=>'FBA信息',
            ];


            // $this->excel_expord($p);
                
            $p['data'] =   $res;
            $p['user_id'] = $params['find_user_id']??0;
            $p['type'] = '走柜详情-导出';
            $job = new \App\Http\Controllers\Jobs\ExcelTaskController;
            try {
                $job::runJob($p);
            } catch(\Exception $e){
                $this->FailBack('失败');
            }
            return $this->SBack([]);
        }else if($posttype==3){
            $p['title']='走柜详情-供应链'.rand(1,100).time();
            $title_list1  = [
                'spu'=>'款号',
                'color'=>'颜色',
                'factory_no'=>'工厂箱号',
                'box_no'=>'箱唛号',
                'box_num'=>'箱数',
                'box_weight'=>'每箱毛重',
                'box_size'=>'箱规',
                'fba_info'=>'FBA信息',
            ];

            foreach ($sizes as $sv) {
                # code...
                $title_list2[$sv]= $sv;
            }

            $p['title_list'] = $title_list1+$title_list2;

            // $this->excel_expord($p);
                
            $p['data'] =  $gyres;
            $p['user_id'] = $params['find_user_id']??0;
            $p['type'] = '走柜详情-导出';

            // var_dump($p);
            $job = new \App\Http\Controllers\Jobs\ExcelTaskController;
            try {
                $job::runJob($p);
            } catch(\Exception $e){
                $this->FailBack('失败');
            }
            return $this->SBack([]);
        }

        
        return $this->SBack($data);
    }

    //获取走柜列表
    public function GetZgList($params){

        $pagenum = isset($params['page']) ? $params['page'] : 1;
        $limit = isset($params['limit']) ? $params['limit'] : 30;

        $page  =  $pagenum- 1;
        if($page!=0){
            $page  =  $page * $limit;
        }

        $res = db::table('self_zg');
        if(isset($params['user_id'])){
            $res = $res->where('user_id',$params['user_id']);
        }
        if(isset($params['id'])){
            $res = $res->where('id',$params['id']);
        }
        $totalNum =  $res ->count();
        $res = $res 
        ->offset($page)
        ->limit($limit)
        ->get();

        foreach ($res as $v) {
            # code...
            $v->account = $this->GetUsers($v->user_id)['account'];

        }
        return $this->SBack(['totalNum'=>$totalNum,'data'=>$res]);
    }

    //运营修改走柜信息
    public function UpdateZg($params){
        $id = $params['zg_id'];
        $fba_content = $params['fba_content'];
        db::table('self_zg')->where('id',$id)->update(['fba_content'=>$fba_content,'status'=>1]);
        foreach ($params['data'] as $v) {
            $up_id = $v['id'];
            $update['box_no'] = $v['box_no'];
            db::table('self_zg_detail')->where('id',$up_id)->update($update);
        }
        return $this->SBack([]);
    }


    //成功返回
    public function SBack($data){
        return['type'=>'success','data'=>$data,'msg'=>'成功'];
    }

    //失败返回
    public function FailBack($msg){
        return['type'=>'fail','data'=>[],'msg'=>$msg];
    }

}